package cn.waka.sys.admin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cn.waka.sys.admin.object.Page;
import cn.waka.sys.admin.object.Resources;

@Component
public class ResourceDao extends BaseDao {

    //计算集数
    public int getResourceTotal(String field, String value) {
        String where = "";
        if (value != "") {
            where = " and " + field + "=" + value;
        }
        String sql = "SELECT count(1) as count FROM s_resource WHERE is_del=0 and status=1 " + where;
        int count = this.queryForObject(sql, Integer.class);
        return count;
    }

    //影片管理总列表
    public Map<String, Object> getResourceList(int start, int size) {
        String sql = "SELECT r.id,r.title,r.img,r.describe,d.`value` AS source_name,r.region,r.language,r.year,r.type,'status',is_selected FROM s_resource r LEFT JOIN w_dic d ON r.source=d.id LIMIT ?,?";
        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                row.put("title", rs.getString(2));
                row.put("img", rs.getString(3));
                row.put("describe", rs.getString(4));
                row.put("source", rs.getString(5));
                row.put("region", dicValue(rs.getInt(6)));
                row.put("language", dicValue(rs.getInt(7)));
                row.put("year", rs.getString(8));
                row.put("type", dicValue(rs.getInt(9)));
                row.put("status", dicValue(rs.getInt(10)));
                row.put("is_selected", dicValue(rs.getInt(11)));
                return row;
            }
        }, new Object[]{start, size});

        return list.size() > 0 ? list.get(0) : null;
    }

    //课程影片列表
    public List<Object> getSchoolResources(Page p) {
        int page = p.getPage();
        int start = (page - 1) * p.getSize();
        String sql = "SELECT id,img,title FROM s_resource WHERE `is_del`=0 AND school !=0 LIMIT ?,?";
        List<Object> list = this.query(sql, new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                row.put("img", rs.getString(2));
                row.put("title", rs.getString(3));
                return row;
            }
        }, start, p.getSize());
        return list;
    }

    //查询总数
    public int findCount(Page p) {
        String sql = "select count(1) from s_resource WHERE `is_del`=0 AND school !=0";
        return this.queryForObject(sql, Integer.class);
    }

    //删除一个
    public int deleteResource(int id) {
        String sql = "DELETE FROM `s_resource` WHERE id =?";
        int rs = this.update(sql, id);
        return rs;
    }

    //根据ID 视频集数
    public int getVideoTotal(int resource_id) {
        String sql = "SELECT count(1) as count FROM s_resource_data WHERE resource_id=?";
        int count = this.queryForObject(sql, Integer.class, resource_id);
        return count;
    }

    public String dicValue(int id) {
        String sql = "SELECT `value` FROM `w_dic` WHERE id=? LIMIT 0,1";
        List<String> list = this.query(sql, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                String value = rs.getString(1);
                return value;
            }
        }, new Object[]{id});

        return list.size() > 0 ? list.get(0) : "";
    }

    public List<Map<String, Object>> findList(Page p, Map<String, String> param) {
        int page = p.getPage();
        int start = (page - 1) * p.getSize();

        String sql = "SELECT id,title,img,`describe`,source,region,`language`,`year`,`type`,`status`,is_selected,c_time,u_time,program FROM s_resource where is_del=0 ";
        Object[] dbparams = null;
        int i = 0;
        if (param != null && param.size() > 0) {
            Set<String> pks = param.keySet();
            dbparams = new Object[2 + param.size()];
            for (String pa : pks) {
                sql += " and " + pa + " like ?";
                dbparams[i] = param.get(pa);
                i++;
            }

            dbparams[param.size()] = start;
            dbparams[param.size() + 1] = p.getSize();
        } else {
            dbparams = new Object[2];
            dbparams[0] = start;
            dbparams[1] = p.getSize();
        }
        sql += " order by id desc limit ?,?";
        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                row.put("title", rs.getString(2));
                row.put("img", rs.getString(3));
                row.put("describe", rs.getString(4));
                row.put("source", rs.getInt(5));
                row.put("region", rs.getInt(6));
                row.put("language", rs.getInt(7));
                row.put("year", rs.getString(8));
                row.put("type", rs.getInt(9));
                row.put("status", rs.getInt(10));
                row.put("is_selected", rs.getInt(11));
                row.put("c_time", rs.getString(12));
                row.put("u_time", rs.getString(13));
                return row;
            }
        }, dbparams);
        return list;
    }

    public int findCount(Page p, Map<String, String> param) {
        String sql = "select count(1) FROM s_resource where is_del=0 ";
        String sql_2 = "SELECT COUNT(1) FROM s_resource WHERE school>0";
        Object[] dbparams = null;
        if (param != null && param.size() > 0) {
            Set<String> pks = param.keySet();
            dbparams = new Object[param.size()];
            int i = 0;
            for (String pa : pks) {
                sql += " and " + pa + " like ?";
                dbparams[i] = param.get(pa);
                i++;
            }
        }
        int c = 0;
        if (param != null && param.size() > 0) {
            c = this.queryForObject(sql, Integer.class, dbparams);
        } else {
            if (p.getOther() == 1) {
                c = this.queryForObject(sql_2, Integer.class);
            } else {
                c = this.queryForObject(sql, Integer.class);
            }
        }
        return c;
    }

    public int getResourceNum(int id) {
        String sql = "select count(1) FROM s_resource_data where is_del=0 and resource_id=?";
        int c = this.queryForObject(sql, Integer.class, id);
        return c;
    }

    //是否精选
    public int updateSelected(int id, int is_selected) {
        String usql = "UPDATE s_resource SET is_selected =? WHERE id=?";
        int rs = this.update(usql, is_selected, id);
        return rs;
    }

    public Resources findById(int id) {
        String sql = "SELECT id,title,img,`describe`,source,region,`language`,`year`,`type`,`status`,is_selected,subtitle,channel,age,school,program,ip_name,ip_img FROM s_resource where id=?";
        List<Resources> list = this.query(sql, new RowMapper<Resources>() {
            @Override
            public Resources mapRow(ResultSet rs, int rowNum) throws SQLException {
                Resources row = new Resources();
                row.setId(rs.getInt(1));
                row.setTitle(rs.getString(2));
                row.setImg(rs.getString(3));
                row.setDescribe(rs.getString(4));
                row.setSource(rs.getInt(5));
                row.setRegion(rs.getInt(6));
                row.setLanguage(rs.getInt(7));
                row.setYear(rs.getInt(8));
                row.setType(rs.getInt(9));
                row.setStatus(rs.getBoolean(10));
                row.setSelected(rs.getBoolean(11));
                row.setSubtitle(rs.getString(12));
                row.setChannel(rs.getInt(13));
                row.setAge(rs.getInt(14));
                row.setSchool(rs.getInt(15));
                row.setProgram(rs.getInt(16));
                row.setIpname(rs.getString(17));
                row.setIpimg(rs.getString(18));
                return row;
            }
        }, id);
        return list.size() > 0 ? list.get(0) : null;
    }

    public int insert(final Resources r) {
        KeyHolder holder = new GeneratedKeyHolder();
        this.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                String sql = "insert into s_resource(title,subtitle,source,`language`,region,`year`,age,`type`,is_selected,school,channel,program,`describe`,img,u_time,ip_name,ip_img)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?)";
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, r.getTitle());
                ps.setString(2, r.getSubtitle());
                ps.setInt(3, r.getSource());
                ps.setInt(4, r.getLanguage());
                ps.setInt(5, r.getRegion());
                ps.setInt(6, r.getYear());
                ps.setInt(7, r.getAge());
                ps.setInt(8, r.getType());
                ps.setBoolean(9, r.isSelected());
                ps.setInt(10, r.getSchool());
                ps.setInt(11, r.getChannel());
                ps.setInt(12, r.getProgram());
                ps.setString(13, r.getDescribe());
                ps.setString(14, r.getImg());
                ps.setString(15, r.getIpname());
                ps.setString(16, r.getIpimg());

                return ps;
            }
        }, holder);
        int id = holder.getKey().intValue();
        return id;
    }

    public int update(Resources r) {
        int rs = this.update("update s_resource set title=?,subtitle=?,source=?,`language`=?,region=?,`year`=?,age=?,`type`=?,is_selected=?,school=?,channel=?,program=?,`describe`=?,img=?,u_time=now(),ip_name=?,ip_img=? where id=?",
                r.getTitle(),
                r.getSubtitle(),
                r.getSource(),
                r.getLanguage(),
                r.getRegion(),
                r.getYear(),
                r.getAge(),
                r.getType(),
                r.isSelected(),
                r.getSchool(),
                r.getChannel(),
                r.getProgram(),
                r.getDescribe(),
                r.getImg(),
                r.getIpname(),
                r.getIpimg(),
                r.getId());
        return rs;
    }

    public List<Map<String, Object>> resourcePlayLogs(Page p, Map<String, String> param) {
        int page = p.getPage();
        int start = (page - 1) * p.getSize();

        String sql = "SELECT login , resource_id , title ,f.`img`, logId , f.`uid` ,play_status ,play_type,create_time,uname FROM (SELECT resource_id , title ,img , logId , uid ,play_status ,play_type,create_time FROM (SELECT a.`id` logId , play_id, play_type,a.`play_status` , a.`play_time` , video_id , box_id ,uid , create_time FROM `s_resource_play_log` a JOIN `s_resource_play` b ON a.`play_id` = b.`id` ) c JOIN `s_resource_data` d ON c.`video_id` = d.`id` ORDER BY create_time DESC) f  JOIN `s_user` e ON e.`uid` = f.`uid` WHERE login IS NOT NULL ";
        Object[] dbparams = null;
        int i = 0;
        if (param != null && param.size() > 0) {
            Set<String> pks = param.keySet();
            dbparams = new Object[2 + param.size()];
            for (String pa : pks) {
                sql += " and " + pa + " like ?";
                dbparams[i] = param.get(pa);
                i++;
            }

            dbparams[param.size()] = start;
            dbparams[param.size() + 1] = p.getSize();
        } else {
            dbparams = new Object[2];
            dbparams[0] = start;
            dbparams[1] = p.getSize();
        }
        sql += " limit ?,?";
        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("login", rs.getString(1));
                row.put("resource_id", rs.getInt(2));
                row.put("title", rs.getString(3));
                row.put("img", rs.getString(4));
                row.put("logId", rs.getInt(5));
                row.put("uid", rs.getInt(6));
                row.put("play_status", rs.getInt(7));
                row.put("play_type", rs.getInt(8));
                row.put("create_time", rs.getTimestamp(9));
                row.put("uname", rs.getString(10));
                return row;
            }
        }, dbparams);
        return list;
    }

    public int playLogCount(Page p, Map<String, String> param) {
        String sql = "select count(1) FROM (SELECT resource_id , title ,img , logId , uid ,play_status ,play_type,create_time FROM (SELECT a.`id` logId , play_id, play_type,a.`play_status` , a.`play_time` , video_id , box_id ,uid , create_time FROM `s_resource_play_log` a JOIN `s_resource_play` b ON a.`play_id` = b.`id` ) c JOIN `s_resource_data` d ON c.`video_id` = d.`id` ORDER BY create_time DESC) f  JOIN `s_user` e ON e.`uid` = f.`uid` WHERE login IS NOT NULL ";
        Set<String> pks = param.keySet();
        Object[] dbparams = new Object[param.size()];
        int i = 0;
        if (param.size() > 0) {
            for (String pa : pks) {
                sql += " and " + pa + " like ?";
                dbparams[i] = param.get(pa);
                i++;
            }
        }
        int c = 0;
        if (param.size() > 0) {
            c = this.queryForObject(sql, Integer.class, dbparams);
        } else {
            c = this.queryForObject(sql, Integer.class);
        }
        return c;
    }


    public int del(int id) {
        int r = this.update("update s_resource set is_del=1 where id=?", id);
        return r;
    }

    public int delDataByResource(int resource_id) {
        int r = this.update("update s_resource_data set is_del=1 where resource_id=?", resource_id);
        return r;
    }

    public int updateStatus(int id, int status) {
        int r = this.update("update s_resource set status=? where id=?", status, id);
        return r;
    }

    public int delData(int id) {
        int r = this.update("update s_resource_data set is_del=1 where id=?", id);
        return r;
    }
}
